/*----------------------------------------\
| To read in data from an excel spreadsheet; |
| Note: cause in order to read the excel spreadsheet, the file must |
| be opened in order for SAS to recognize it, so within some |
| part of the function, it run the excel.exe first then open |
| the file within it; please make the path of the excel.exe is |
| correct. |
|-------------------------------------------|
|--------------------------------------------------------------------|
|---------------------------|
| Arguments needed: |
| path = the directory of the excel file; |
| if filename is specified, this is the same as "directory";|
| if filename is not specified, this is the path of the file|
| instead of the directory of the file. |
| directory= the directory of the excel file you want to read in; |
| this parameter is the same as path if filename is |
| specified; |
| filename= the exact filename you want to read in; |
| if missing, the path must be the path of the excel |
| file (e.g. 'c:\...\filename.xls'; |
| sheet= the sheet name you want to read; |
| default is "sheet1"; |
| start= the starting position to read in the spread sheet; |
| if end is missing, the start must be in the form of |
| r1c1:r12c32; the function will use the part before ":" as |
| start, the part behind ":" as end; |
| end = the ending position to read; |
| stop= the position you want to stop reading; |
| varfmt= the variables and the corresponding formats for each |
| columns, you want to use; |
| note the variables and the corresponding formats must be |
| entered in pairs, separated by ' ', and pairs must be |
| separated by '|'; |
| if varfmt not provided, the function will use the first |
| row of the area specified to be the variable names; |
|-----------------------------|
|--------------------------------------------------------------------|
|---------------------------------------|
| Example: |
| %excelread(path=Y:\CLINICAL\TACHY\BIOSTAT\Duo\Projects\VR-IDE\ |
| stat\RepOC\IDE Inv.xls, |
| start=r1c1:r50c8, output=invinfo); |
| Usage: %excelread(path=.,filename=., sheet=sheet1,start=r1c1, |
| end=r9000c15,stop='.',directory=, varfmt=. .|,output=);|
\----------------------------------------*/
%macro excelread(path=,filename=, sheet=sheet1,start=r1c1,end=r9000c15,stop=,
directory=, varfmt=|,output=);
/*--------------------------------------------\
| Copy Right: Duo Zhou; |
| Created: 3-5-2001 10:12pm; |
| Modified: 1-15-2002 9:43pm; |
| Purpose: Read in an excel spread sheet; |
\--------------------------------------------*/
options noxwait noxsync; x 'Exit';
%if (%length(&path) >=3) %then %let path=%sysfunc(dequote(&path));
%else %if (%quote(&directory) ne) %then %let path=%sysfunc(dequote(&directory));
%if (%length(&filename) > 4 )%then %do;
%let filename=%qscan(&filename,1,%str(''""));
%let filename=%sysfunc(translate(%quote(&filename), %str(\), %str(/)));
%if (%index(%quote(&filename), %quote(\))) %then %do;
%let filepath=%quote(%trim(%quote(%left(%quote(&filename)))));
%let filename=%sysfunc(reverse(%qscan(%quote(%sysfunc(reverse(%quote(&filepath)))), 1, %str(\))));
%let path=%substr(&filepath, 1, %eval(%length(&filepath)-%length(&filename)));
%end;
%end;
%else %if (%length(&path) >=3) %then %do;
%let path=%sysfunc(translate(%quote(&path), %str(\), %str(/)));
%if (%index(%substr(%quote(&path),%eval(%length(&path)-4),5), %quote(.))) %then %do;
%let filename=%qscan(%quote(&path), %words(&path, dlm=%str(\)), %str(\));
%let path=%substr(%quote(&path), 1, %eval(%length(&path)-%length(&filename)));
%end;
%else %do;
%put ==> Alert! You did not specify a filename, and the path you provided does not include;
%put +++ a filename. Please provide a valid filename or the path of the file.;
%goto finish;
%end;
%end;
%else %do;
%put ==> Alert! No valid filename or path provided!;
%goto finish;
%end;
%if (%length(&sheet) >=1) %then %let sheet=%qscan(&sheet,1,%str(''""));
%else %let sheet=sheet1;
%if (%length(&path)>=3) %then %do;
%if (%substr(&path, %length(&path), 1) ne \) %then %let path=&path.\;
%end;
%else %do;
%put ==> Alert! No valid path provided!;
%goto finish;
%end;
%if (%index(%quote(&start),%quote(:))) %then %do;
%let vend=%qscan(%quote(&start), 2, %quote(:));
%let start=%qscan(%quote(&start), 1, %quote(:));
%if (%quote(&end) ne and %quote(&vend) ne) %then %do;
%put ==> Alert! The "&end" will be overwritten by "&vend".;
%let end=&vend;
%end;
%end;
%local nvars npairs nfmts numi _i_ _j_ _k_ srow scol erow ecol;
%let infile=&path.&filename.; %let resize=;
%if (%length(&varfmt) < 3) %then %do;
%let resize=1;
%let srow=%qscan(%quote(&start), 1, %str(RrCc));
%let scol=%qscan(%quote(&start), 2, %str(RrCc));
%let erow=%qscan(%quote(&end), 1, %str(RrCc));
%let ecol=%qscan(%quote(&end), 2, %str(RrCc));
%let vnamestart=r%trim(%left(&srow))c%trim(%left(&scol));
%let vnameend=r%trim(%left(&srow))c%trim(%left(&ecol));
%let npairs=%eval(%eval(&ecol+1)-&scol);
%if (%sysfunc(floor(%sysevalf(1048576/&npairs))) >= 32767) %then %let flen=32767;
%else %let flen=%sysfunc(floor(%sysevalf(1048576/&npairs)));
%let varnameflen=$%trim(%left(&flen)).;
%if (%quote(&infile) ne) %then %do;
%let dsid=%sysfunc(fileexist(&infile));
%if &dsid %then %do;
filename longname dde "excel|&path.[&filename.]&sheet.!&vnamestart.:&vnameend." notab LRECL=1048576;
data _null_;
infile longname dlm='09'x dsd missover;
array tmp{*} &varnameflen tempvar1 - tempvar%left(&npairs);
input tmp{*} : &varnameflen;
format varfmt $32767. _tmp_ $2.;
varfmt='';
do i=1 to %trim(%left(&npairs));
tmp{i}=compress(tmp{i}, "`~!@#$%^&*()-+={}][|\:';<,>.?/");
tmp{i}=compress(tmp{i}, '"');
if length(tmp{i}) > 30 then do;
j=1;
varfmt=lowcase(scan(tmp{i},j));
substr(varfmt, 1, 1)=upcase(substr(varfmt, 1, 1));
varfmt='_'||trimn(left(varfmt))||'_';
if length(varfmt)>32 then do;
varfmt=substr(trimn(left(varfmt)), 1, 31)||'_';
end;
else do;
do while((not missing(scan(trimn(left(tmp{i})), j+1))) and (length(varfmt)<30));
j=j+1;
_tmp_=upcase(substr(scan(trimn(left(tmp{i})), j), 1, 1));
varfmt=trimn(left(varfmt))||trimn(left(_tmp_));
end;
varfmt=trimn(left(varfmt))||'_'||' '||"$%trim(%left(&flen)).|";
end;
output;
end;
else do;
tmp{i}='_'||tranwrd(trimn(left(upcase(tmp{i}))), ' ', '_')||'_';
varfmt=trimn(left(varfmt))||trimn(left(tmp{i}))||' '||"$%trim(%left(&flen)).|";
end;
end;
call symput('varfmt', varfmt);
run; %put varfmt is &varfmt.;
%end;
%else %do;
%put ==> Alert: File "&infile" doesn%str(%')t exist.;
%goto finish;
%end;
%end;
%else %do;
%put ==> Alert: I need an input file.;
%goto finish;
%end;
%let start=r%trim(%left(%eval(&srow+1)))c%trim(%left(&scol));
%end;
%if (%length(&varfmt) >=3) %then %do;
%let npairs=%words(&varfmt,dlm=%str(|,));
%do _j_=1 %to &npairs;
%let pair&_j_=%qscan(&varfmt,&_j_,%str(|,));
%let var&_j_=%qscan(&&pair&_j_,1,%str( ));
%let fmt&_j_=%qscan(&&pair&_j_,2,%str( ));
%end;
%end;
%else %do;
%put ==> Alert! The contents of "&vnamestart.:&vnameend." do not have valid variable names;
%put +++ and you did not provide any variable names and formats!;
%goto finish;
%end;
%if (%quote(&infile) ne) %then %do;
%let dsid=%sysfunc(fileexist(&infile));
%if &dsid %then %do;
filename longname dde "excel|&path.[&filename.]&sheet.!&start.:&end." notab LRECL=1048576;
data &output;
infile longname dlm='09'x dsd missover;
format &var1 &fmt1;
input %do _k_=1 %to &npairs;
&&var&_k_
%if &&fmt&_k_ ne %then %do;
%if (%index(&&fmt&_k_,:)) %then %do; &&fmt&_k_ %end;
%else %do; :&&fmt&_k_ %end;
%end;
%end;;
run;
%if (%length(&stop) >1) %then %do;
data &output;
set &output;
count+1;
if (upcase(&var1) eq upcase(&stop)) then do;
call symput('stopsign',_n_);
end;
run;
data &output;
set &output;
if count < &stopsign;
run;
%end;
%if (%quote(&resize) eq %quote(1)) %then %do;
%resizec(indata=&output);
%end;
%end;
%else %do;
%put ==> Alert: File "&infile" doesn%str(%')t exist.;
%goto finish;
%end;
%end;
%else %do;
%put ==> Alert: I need an input file.;
%goto finish;
%end;
%finish:
%mend excelread;